import boto3
import os
import datetime
import time
from datetime import datetime
from datetime import timedelta
import openpyxl as xl
import sys
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell import MergedCell
import pandas as pd
import re
'''
本程序主要解决Excel中各sheet的数据解析
将数据解析成可分析的Dataframe格式
方便后续的数据加工处理
'''
def main():
    wb = xl.load_workbook("钢协反馈数据-主要财务指标2024年11月.xlsx")
    result0 = '202411'
    print(wb.sheetnames)
    for sheet_name in wb.sheetnames:
        print(sheet_name)
    sheet_name_list_1 = ['大中型钢铁企业主要财务指标汇总表']
    sheet_name_list_2 = ['营业收入前20名企业情况表', '实现利税前20名企业情况表', '利润总额前20名企业情况表',
                         '1000万吨钢以上企业实现利润表', '500万吨钢以上企业实现利润表', '累计亏损企业情况表']
    sheet_name_list_3 = ['营业收入,营业成本', '实现利税,利润总额', '销售费用,管理费用',
                         '研发费用,当期计提折旧额', '财务费用,利息支出', '资产总额,负债总额', '流动资产,流动负债', '月末存货,产成品',
                         '应收账款,应收票据', '应付账款,应付票据', '短期借款,长期借款', '应交税费总额,已交税费总额',
                         '工业总产值,劳动生产总值', '工资总额,期末职工人数', '销售利润率,净资产收益率', '资产负债率,全员劳动生产率',
                         '流动比率,速动比率', '存货周转次数,应收账款周转次数', '吨钢利润,吨钢期间费用', '吨钢销售费用,吨钢管理费用',
                         '吨钢研发费用,吨钢财务费用', '吨钢折旧,吨钢工资']
    for tmp_sheet_name in wb.sheetnames:
        print(tmp_sheet_name)
        if tmp_sheet_name in sheet_name_list_1:
            df = sheetname_2_df(wb, tmp_sheet_name)
            mapping = {'序号': 'SERIAL_NUM',
                       '项目': 'INDEX_NAME',
                       '本月数': 'CURR_MONTH_VALUE',
                       '本年累计': 'CURR_YEAR_VALUE',
                       '去年同期': 'LAST_YEAR_VALUE',
                       '增减(%)': 'RATIO_VALUE'}
            df.rename(columns=mapping, inplace=True)
            tmp_now = datetime.now().strftime('%Y%m%d%H%M%S')
            new_df = df.reset_index()
            new_df.rename(columns={'index': 'random_id'}, inplace=True)
            new_df['new_tag'] = new_df['random_id'].astype(str)
            new_df['REC_ID'] = new_df.apply(lambda x: str(result0) + '-' + str(x['new_tag']), axis=1)
            new_df['LOADER'] = 'system'
            new_df['LOAD_TIME'] = tmp_now
            new_df['DATE_TIME'] = result0
            new_df.drop(['random_id'], axis=1, inplace=True)
            new_df.drop(['new_tag'], axis=1, inplace=True)
            new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']] = new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']].apply(pd.to_numeric, errors='coerce')
            desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'INDEX_NAME', 'CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']
            new_df = new_df.reindex(columns=desired_order)
            # new_df.to_csv('D:/repos/sicost/202411/' + tmp_sheet_name + '.csv', index=False)
            writer = pd.ExcelWriter('D:/repos/sicost/202411/' + tmp_sheet_name + '.xlsx')
            new_df.to_excel(writer, sheet_name='Sheet1', index=False)
            writer.save()
            print('FINISH')
        if tmp_sheet_name in sheet_name_list_2:
            df = sheetname_2_df(wb, tmp_sheet_name)
            mapping = {'序号': 'SERIAL_NUM',
                       '单位': 'COMPANY_NAME',
                       '本月数': 'CURR_MONTH_VALUE',
                       '本年累计': 'CURR_YEAR_VALUE',
                       '去年同期': 'LAST_YEAR_VALUE',
                       '增减(%)': 'RATIO_VALUE'}
            df.rename(columns=mapping, inplace=True)
            tmp_now = datetime.now().strftime('%Y%m%d%H%M%S')
            new_df = df.reset_index()
            new_df.rename(columns={'index': 'random_id'}, inplace=True)
            new_df['new_tag'] = new_df['random_id'].astype(str)
            new_df['REC_ID'] = new_df.apply(lambda x: str(result0) + '-' + str(x['new_tag']), axis=1)
            new_df['LOADER'] = 'system'
            new_df['LOAD_TIME'] = tmp_now
            new_df['DATE_TIME'] = result0
            new_df.drop(['random_id'], axis=1, inplace=True)
            new_df.drop(['new_tag'], axis=1, inplace=True)
            new_df[['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE']] = new_df[
                ['CURR_MONTH_VALUE', 'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE']].apply(pd.to_numeric, errors='coerce')
            new_df['RATIO_VALUE'] = new_df['RATIO_VALUE'].astype(str)
            desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'COMPANY_NAME', 'CURR_MONTH_VALUE',
                             'CURR_YEAR_VALUE', 'LAST_YEAR_VALUE', 'RATIO_VALUE']
            new_df = new_df.reindex(columns=desired_order)
            # new_df.to_csv('D:/repos/sicost/202411/' + tmp_sheet_name + '.csv', index=False)
            writer = pd.ExcelWriter('D:/repos/sicost/202411/' + tmp_sheet_name + '.xlsx')
            new_df.to_excel(writer, sheet_name='Sheet1', index=False)
            writer.save()
            print('FINISH')
        if tmp_sheet_name in sheet_name_list_3:
            tmp_sheet_name = tmp_sheet_name.replace('，', ',')
            if ',' in tmp_sheet_name:
                parts = tmp_sheet_name.split(',')
                part1 = parts[0]
                part2 = parts[1]
                df = sheetname_2_df(wb, tmp_sheet_name)
                column_name_list = list(df.columns)
                mapping = {'序号': 'SERIAL_NUM',
                           '单位': 'COMPANY_NAME'}
                for column_name_tmp in column_name_list:
                    if part1 in column_name_tmp:
                        if '本月数' in column_name_tmp:
                            mapping[column_name_tmp] = 'CURR_MONTH_VALUE_1'
                        if '本年累计' in column_name_tmp:
                            mapping[column_name_tmp] = 'CURR_YEAR_VALUE_1'
                        if '去年同期' in column_name_tmp:
                            mapping[column_name_tmp] = 'LAST_YEAR_VALUE_1'
                        if '增减' in column_name_tmp:
                            mapping[column_name_tmp] = 'RATIO_VALUE_1'
                    elif part2 in column_name_tmp:
                        if '本月数' in column_name_tmp:
                            mapping[column_name_tmp] = 'CURR_MONTH_VALUE_2'
                        if '本年累计' in column_name_tmp:
                            mapping[column_name_tmp] = 'CURR_YEAR_VALUE_2'
                        if '去年同期' in column_name_tmp:
                            mapping[column_name_tmp] = 'LAST_YEAR_VALUE_2'
                        if '增减' in column_name_tmp:
                            mapping[column_name_tmp] = 'RATIO_VALUE_2'
                    else:
                        continue
                df.rename(columns=mapping, inplace=True)
                tmp_now = datetime.now().strftime('%Y%m%d%H%M%S')
                new_df = df.reset_index()
                new_df.rename(columns={'index': 'random_id'}, inplace=True)
                new_df['new_tag'] = new_df['random_id'].astype(str)
                new_df['REC_ID'] = new_df.apply(lambda x: str(result0) + '-' + str(x['new_tag']), axis=1)
                new_df['LOADER'] = 'system'
                new_df['LOAD_TIME'] = tmp_now
                new_df['DATE_TIME'] = result0
                new_df.drop(['random_id'], axis=1, inplace=True)
                new_df.drop(['new_tag'], axis=1, inplace=True)
                new_column_name_list = list(new_df.columns)
                change_list1 = ['CURR_MONTH_VALUE_1', 'CURR_YEAR_VALUE_1', 'LAST_YEAR_VALUE_1']
                change_list2 = ['CURR_MONTH_VALUE_2', 'CURR_YEAR_VALUE_2', 'LAST_YEAR_VALUE_2']
                change_list1_final = []
                desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'COMPANY_NAME']
                for change_column_tmp in change_list1:
                    if change_column_tmp in new_column_name_list:
                        change_list1_final.append(change_column_tmp)
                        desired_order.append(change_column_tmp)
                desired_order.append('RATIO_VALUE_1')
                for change_column_tmp in change_list2:
                    if change_column_tmp in new_column_name_list:
                        change_list1_final.append(change_column_tmp)
                        desired_order.append(change_column_tmp)
                desired_order.append('RATIO_VALUE_2')
                new_df[change_list1_final] = new_df[change_list1_final].apply(pd.to_numeric, errors='coerce')
                new_df['RATIO_VALUE_1'] = new_df['RATIO_VALUE_1'].astype(str)
                new_df['RATIO_VALUE_2'] = new_df['RATIO_VALUE_2'].astype(str)
                new_df = new_df.reindex(columns=desired_order)
                # new_df.to_csv('D:/repos/sicost/202411/' + tmp_sheet_name + '.csv', index=False)
                writer = pd.ExcelWriter('D:/repos/sicost/202411/' + tmp_sheet_name + '.xlsx')
                new_df.to_excel(writer, sheet_name='Sheet1', index=False)
                writer.save()
                print('FINISH')
            else:
                part1 = tmp_sheet_name
                df = sheetname_2_df(wb, tmp_sheet_name)
                column_name_list = list(df.columns)
                mapping = {'序号': 'SERIAL_NUM',
                           '单位': 'COMPANY_NAME'}
                for column_name_tmp in column_name_list:
                    if part1 in column_name_tmp:
                        if '本月数' in column_name_tmp:
                            mapping[column_name_tmp] = 'CURR_MONTH_VALUE_1'
                        if '本年累计' in column_name_tmp:
                            mapping[column_name_tmp] = 'CURR_YEAR_VALUE_1'
                        if '去年同期' in column_name_tmp:
                            mapping[column_name_tmp] = 'LAST_YEAR_VALUE_1'
                        if '增减' in column_name_tmp:
                            mapping[column_name_tmp] = 'RATIO_VALUE_1'
                df.rename(columns=mapping, inplace=True)
                tmp_now = datetime.now().strftime('%Y%m%d%H%M%S')
                new_df = df.reset_index()
                new_df.rename(columns={'index': 'random_id'}, inplace=True)
                new_df['new_tag'] = new_df['random_id'].astype(str)
                new_df['REC_ID'] = new_df.apply(lambda x: str(result0) + '-' + str(x['new_tag']), axis=1)
                new_df['LOADER'] = 'system'
                new_df['LOAD_TIME'] = tmp_now
                new_df['DATE_TIME'] = result0
                new_df.drop(['random_id'], axis=1, inplace=True)
                new_df.drop(['new_tag'], axis=1, inplace=True)
                new_column_name_list = list(new_df.columns)
                change_list1 = ['CURR_MONTH_VALUE_1', 'CURR_YEAR_VALUE_1', 'LAST_YEAR_VALUE_1']
                change_list1_final = []
                desired_order = ['REC_ID', 'LOADER', 'LOAD_TIME', 'DATE_TIME', 'SERIAL_NUM', 'COMPANY_NAME']
                for change_column_tmp in change_list1:
                    if change_column_tmp in new_column_name_list:
                        change_list1_final.append(change_column_tmp)
                        desired_order.append(change_column_tmp)
                desired_order.append('RATIO_VALUE_1')
                new_df[change_list1_final] = new_df[change_list1_final].apply(pd.to_numeric, errors='coerce')
                new_df['RATIO_VALUE_1'] = new_df['RATIO_VALUE_1'].astype(str)
                new_df = new_df.reindex(columns=desired_order)
                # new_df.to_csv('D:/repos/sicost/202411/' + tmp_sheet_name + '.csv', index=False)
                writer = pd.ExcelWriter('D:/repos/sicost/202411/' + tmp_sheet_name + '.xlsx')
                new_df.to_excel(writer, sheet_name='Sheet1', index=False)
                writer.save()
                print('FINISH')
def get_ord_list(str):
    return [ord(i) for i in str]
def calcu_approx(str1, str2):
    def dot(A, B):
        return (sum(a * b for a, b in zip(A, B)))
    def cosine_similarity(a, b):
        return dot(a, b) / ((dot(a, a) ** .5) * (dot(b, b) ** .5))
    ord_list1 = get_ord_list(str1)
    ord_list2 = get_ord_list(str2)
    max_ord = max(max(ord_list1), max(ord_list2))+1
    ord_dense_list1 = [0]*max_ord
    ord_dense_list2 = [0]*max_ord
    for i in ord_list1:
        ord_dense_list1[i] = 1
    for i in ord_list2:
        ord_dense_list2[i] = 1
    return cosine_similarity(ord_dense_list1, ord_dense_list2)
def add_column_remark(list):
    set_list = set(list)
    if len(set_list) == len(list):
        print('无重复，不需要拼接')
    else:
        print('有重复，需要拼接')
    list2 = []
    # print(len(list))
    for i in range(0, len(list)):
        # print(list.count(list[i]))
        # print(list[i])
        if list.count(list[i]) == 1:
            list2.append(list[i])
        else:
            # print('重复元素出现的位置索引分别是 = ', [j for j, v in enumerate(list) if v == list[i]])
            chongfuidlist = [j for j, v in enumerate(list) if v == list[i]]
            chongfulist = []
            for l in range(1, chongfuidlist[0] + 1):
                # print('l')
                # print(l)
                for k in chongfuidlist:
                    if k - l >= 0:
                        # print(k)
                        # print(list[k])
                        # print(list[k - l] + list[k])
                        chongfulist.append(list[k - l] + list[k])
                    else:
                        chongfulist.append(list[k])
                if len(set(chongfulist)) == len(chongfulist):
                    # print('无重复')
                    # print(list[i - l] + list[i])
                    list2.append(list[i - l] + list[i])
                    break
                else:
                    # print('有重复')
                    chongfulist = []
    return list2
def sheetname_2_df(wb,sheet_name):
    sheet_ = wb[sheet_name]
    max_row_num = sheet_.max_row  # 获取最大行数
    max_col_num = sheet_.max_column  # 获取最大列数
    data_list = []
    merge_tag_list = []
    title_list = []
    title_num = 0
    column_num = 0
    hastitlebutnocolumn = 0
    df_num = 1
    need_merge = 0
    my_dict = {}
    my_dict2 = {}
    need_merge_list = []
    for row_index in range(1, max_row_num + 1):
        print('现在解析行数：', row_index)
        col_list = []
        line_type = ''
        merge_tag_sum0 = 0
        none_num_sum0 = 0
        for col_index in range(1, max_col_num + 1):
            merge_tag0 = 0
            none_num0 = 0
            cell0 = sheet_.cell(row=row_index, column=col_index)
            if isinstance(cell0, MergedCell):
                merge_tag0 = 1
            if cell0.value is None:
                none_num0 = 1
            none_num_sum0 = none_num_sum0 + none_num0
            merge_tag_sum0 = merge_tag_sum0 + merge_tag0
        if none_num_sum0 == max_col_num and title_num == 0:
            print('该行为空行')
            print('将sheet名赋值给title')
            line_type = 'title'
            title_num = title_num + 1
            title_name = sheet_name

            title_list.append(sheet_.cell(row=row_index, column=1).value)
            continue
        elif none_num_sum0 != max_col_num and title_num == 0:
            print('非空行默认为标题')
            for col_index in range(1, max_col_num + 1):
                cell = sheet_.cell(row=row_index, column=col_index)
                if cell.value is not None:
                    line_type = 'title'
                    title_num = title_num + 1
                    title_name = cell.value
                    title_list.append(sheet_.cell(row=row_index, column=1).value)
                    break
                else:
                    continue
        # if row_index == 1:
        #     merge_tag_sum = 0
        #     none_num_sum = 0
        #     for col_index in range(1, max_col_num + 1):
        #         merge_tag = 0
        #         none_num = 0
        #         cell = sheet_.cell(row=row_index, column=col_index)
        #         if isinstance(cell, MergedCell):
        #             merge_tag = 1
        #         if cell.value is None:
        #             none_num = 1
        #         none_num_sum = none_num_sum + none_num
        #         merge_tag_sum = merge_tag_sum + merge_tag
        #         col_list.append(cell.value)
        #     if '大中' in sheet_.cell(row=row_index, column=1).value and none_num_sum == max_col_num - 1:
        #         line_type = 'title'
        #         title_num = title_num + 1
        #         title_name = sheet_.cell(row=row_index, column=1).value
        #         title_list.append(sheet_.cell(row=row_index, column=1).value)
        #         #暂时默认首行必定为title
        else:
            col_list = []
            merge_tag_sum = 0
            none_num_sum = 0
            for col_index in range(1, max_col_num + 1):
                merge_tag = 0
                none_num = 0
                cell = sheet_.cell(row=row_index, column=col_index)
                if isinstance(cell, MergedCell):
                    merge_tag = 1
                if cell.value is None:
                    none_num = 1
                none_num_sum = none_num_sum + none_num
                merge_tag_sum = merge_tag_sum + merge_tag
                col_list.append(cell.value)
            remark_num_sum = 0
            res = []
            for i in col_list:
                if type(i) == str:
                    i = i.replace(" ", "")
                    if i[0:2] in ['表1', '表2', '表3', '表4', '表5', '表6', '表7', '表8', '表9', '备：', '备:', '注：', '注:'] or (
                            len(i) > 2 and i[0:2] in ['单位', '备注']):
                        line_type = 'remark'
                if i is not None:
                    res.append(i)
            if line_type == 'remark':
                continue
            set_res = set(res)
            if len(set_res) == len(res):
                print('去除None后行list无重复')
            else:
                print('去除None后行list有重复')
            if title_num > column_num:
                hastitlebutnocolumn = 1
            else:
                hastitlebutnocolumn = 0
            if merge_tag_sum == 0 and len(set_res) == len(res) and hastitlebutnocolumn == 1:
                line_type = 'column'
                column_num = column_num + 1
                column_list = res
                # print(len(column_list))
            elif merge_tag_sum == 0 and len(set_res) != len(res) and hastitlebutnocolumn == 1:
                print('columnlist有重复数据需要添加标签去重')
                list_new = add_column_remark(res)
                line_type = 'column'
                column_num = column_num + 1
                column_list = list_new
                print('当前column_list为:')
                print(column_list)
                # print(len(column_list))
            elif merge_tag_sum != 0 and hastitlebutnocolumn == 0:
                print('出现合并单元格应该是纵向！！！合并格，已经生成的column应该作废，与本行合并共同作为column')
                print('重置column')
                col_list = []
                for col_index in range(1, max_col_num + 1):
                    merge_tag = 0
                    none_num = 0
                    cell = sheet_.cell(row=row_index, column=col_index)
                    if isinstance(cell, MergedCell):
                        merge_tag = 1
                    if cell.value is None:
                        none_num = 1
                    none_num_sum = none_num_sum + none_num
                    merge_tag_sum = merge_tag_sum + merge_tag
                    value1 = column_list[col_index - 1]
                    value2 = cell.value
                    if type(value1) == str:
                        value1 = value1.replace(" ", "")
                    if type(value2) == str:
                        value2 = value2.replace(" ", "")
                    if value1 is None:
                        value1 = ''
                    if value2 is None:
                        value2 = ''
                    value = str(value1) + str(value2)
                    col_list.append(value)
                res = []
                for i in col_list:
                    if type(i) == str:
                        i = i.replace(" ", "")
                    res.append(i)
                if len(set(res)) != len(res):
                    res = add_column_remark(res)
                line_type = 'column'
                column_num = column_num - 1
                column_num = column_num + 1
                column_list = res
                print('当前column_list为:')
                print(column_list)
                # print(len(column_list))
            elif need_merge == 0 and merge_tag_sum != 0 and hastitlebutnocolumn == 1:
                print('出现合并单元格应该是横向!!!合并格，并且该行可能是需要拼接下一行共同作为column')
                need_merge = 1
                col_list = []
                for col_index in range(1, max_col_num + 1):
                    merge_tag = 0
                    none_num = 0
                    cell = sheet_.cell(row=row_index, column=col_index)
                    if isinstance(cell, MergedCell):
                        merge_tag = 1
                        for merged_range in sheet_.merged_cell_ranges:
                            if cell.coordinate in merged_range:
                                cell = sheet_.cell(row=merged_range.min_row, column=merged_range.min_col)
                                break
                    if cell.value is None:
                        none_num = 1
                    none_num_sum = none_num_sum + none_num
                    merge_tag_sum = merge_tag_sum + merge_tag
                    col_list.append(cell.value)
                # print(col_list)
                need_merge_list = col_list
            elif need_merge == 1 and hastitlebutnocolumn == 1:
                print('需要merge单元格生成column，横赋值，纵不赋值，两行合并')
                col_list = []
                for col_index in range(1, max_col_num + 1):
                    merge_tag = 0
                    none_num = 0
                    cell = sheet_.cell(row=row_index, column=col_index)
                    if isinstance(cell, MergedCell):
                        merge_tag = 1
                    if cell.value is None:
                        none_num = 1
                    none_num_sum = none_num_sum + none_num
                    merge_tag_sum = merge_tag_sum + merge_tag
                    # print(sheet_.cell(row=row_index-1, column=col_index).value)
                    value1 = need_merge_list[col_index - 1]
                    value2 = cell.value
                    if type(value1) == str:
                        value1 = value1.replace(" ", "")
                    if type(value2) == str:
                        value2 = value2.replace(" ", "")
                    if value1 is None:
                        value1 = ''
                    if value2 is None:
                        value2 = ''
                    value = str(value1) + str(value2)
                    col_list.append(value)
                need_merge_list = []
                need_merge = 0
                res = []
                for i in col_list:
                    if type(i) == str:
                        i = i.replace(" ", "")
                    res.append(i)
                if len(set(res)) != len(res):
                    res = add_column_remark(res)
                line_type = 'column'
                column_num = column_num + 1
                column_list = res
                print('当前column_list为:')
                print(column_list)
                # print(column_list)
            elif merge_tag_sum == 0 and hastitlebutnocolumn == 0:
                print(type(col_list[0]))
                print(col_list[0])
                # print([ord(i) for i in str])
                if type(col_list[0]) == str:
                    if title_list[0] is None or col_list[0] is None:
                        cos = 0
                    else:
                        cos = calcu_approx(title_list[0], col_list[0])
                    print(cos)
                    if cos > 0.5:
                        print('和title相似度高，是title')
                        line_type = 'title'
                        title_num = title_num + 1
                        # print(title_name)
                        # print(data_list)
                        # print(column_list)
                        df = pd.DataFrame(data_list, columns=column_list)
                        i = df_num
                        my_dict[f"df_{i}"] = pd.DataFrame(data_list, columns=column_list)
                        my_dict2[f"df_1{i}"] = pd.DataFrame(data_list, columns=column_list)
                        df_num = df_num + 1
                        # 重新新开一个df
                        data_list = []
                        column_list = []
                        title_name = col_list[0]
                        title_list.append(col_list[0])
                    else:
                        print('和title相似度不高，不是title')
                        line_type = 'data'
                        data_list.append(col_list[0:len(column_list)])
                else:
                    if none_num_sum == max_col_num:
                        print('空行无意义')
                        break
                    else:
                        line_type = 'data'
                        data_list.append(col_list[0:len(column_list)])
    # print(title_name)
    # print(data_list)
    df = pd.DataFrame(data_list, columns=column_list)
    i = df_num
    my_dict[f"df_{i}"] = pd.DataFrame(data_list, columns=column_list)
    my_dict2[f"df_1{i}"] = pd.DataFrame(data_list, columns=column_list)

    merge_id_list = []
    for m in range(0, len(title_list)):
        # print(title_list[0:m])
        new_list = title_list[0:m]
        if title_list[m] in new_list:
            print('title一样，需要合并df')
            # print(m)
            idlist = [j for j, v in enumerate(new_list) if v == title_list[m]]
            # print(idlist)
            merge_title_list_id = idlist[0]
            v = []
            col = my_dict.get(f"df_{merge_title_list_id + 1}").columns.values.tolist()
            v.append(col[0])
            my_dict2[f"df_1{merge_title_list_id + 1}"] = pd.merge(my_dict2.get(f"df_1{merge_title_list_id + 1}"),
                                                                  my_dict.get(f"df_{m + 1}"), on=v, how='left')
            merge_id_list.append(m)
            # print(my_dict2)
    # print(merge_id_list)
    for x in reversed(merge_id_list):
        del my_dict2[f"df_1{x + 1}"]
        del title_list[x]
    # for m in range(0, len(title_list)):
    #     print(title_list[m])
    #     # print(list(my_dict2.keys())[m])
    #     df_name = list(my_dict2.keys())[m]
    #     print(my_dict2.get(f"{df_name}"))
    #     df = my_dict2.get(f"{df_name}")
    #     # df.to_csv('output_0526_' + sheet_name + str(m) + '.csv', index=False)
    m = 0
    print(title_list[m])
    # print(list(my_dict2.keys())[m])
    df_name = list(my_dict2.keys())[m]
    print(my_dict2.get(f"{df_name}"))
    df = my_dict2.get(f"{df_name}")
    return df



if __name__ == '__main__':
    start1 = datetime.now()
    status = main()
    elapsed2 = float((datetime.now() - start1).seconds)
    print("Time Used 4 All ----->>>> %f seconds" % (elapsed2))
    sys.exit(0)
